﻿using System;
using System.ServiceModel;
using System.ServiceModel.Activation;
using System.Data;
using LIB.Core;
using LIB.Data;

[ServiceContract(Namespace = "")]
[AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
public class wdkhp
{
    [OperationContract]
    public string Fs_NHAPDIEM_CH()
    {
        try
        {
            DataTable b_dt_namhoc = c_ht.Fdt_NAM_HOC();
            DataTable b_dt = c_ht.Fdt_BOMON_LKE();
            DataTable b_dt_ng = c_ht.Fdt_NGANH_HOC_LKE();
            DataTable b_dt_nganh = c_ht.Fdt_NGANH_HOC_LKE();

            //Thêm hệ dt
            DataTable b_dt_he = LIB.Data.SqlData.GetData("PHinhThuc_DT_LKE");
            string he = "";
            if (!LibTable.IsNullOrEmtyTable(b_dt_he))
            {
                he = b_dt_he.Rows[0][0].ToString();
            }
            DataTable b_dtkhoa = c_ht.Fdt_KHOA_HEDAOTAO(he);

            //Loại điểm
            DataTable b_dt_diem = LIB.Data.SqlData.GetData("PLOAI_KTRA_LKE");
            return LibTable.FormTableToJson(b_dt_namhoc) + "@" + LibTable.FormTableToJson(b_dt)
                + "@" + LibBang.Fs_BANG_JSON(SqlData.GetData("PLOP_HOC_ALL")) + "@" + LibTable.FormTableToJson(b_dt_ng) + "@" +
                LibTable.FormTableToJson(b_dt_he) + "@" + LibTable.FormTableToJson(b_dtkhoa) + "@" + LibBang.Fs_BANG_JSON(b_dt_diem);
        }
        catch (Exception ex)
        {
            return "loi:" + ex.Message + ":loi";
        }
    }
    //Lấy thông tin môn học theo bộ môn
    [OperationContract]
    public string Fs_MM_BOMON(string id_bm)
    {
        try
        {
            DataTable b_dt_mon = new DataTable();
            b_dt_mon = c_ht.Fdt_MON_NGANH(id_bm);
            return LibBang.Fs_BANG_JSON(b_dt_mon);
        }
        catch (Exception ex)
        {
            return ex.Message;
        }
    }
    [OperationContract]
    public string Fs_SINHVIEN_DK_TIM(object[] a_ten, object[] a_tso)
    {
        try
        {

            DataTable b_dt_tso = LibBang.Fdt_TAO_BANG(LibConvert.Fa_OBJ_STR(a_ten), LibConvert.Fa_OBJ_STR(a_tso));
            if (b_dt_tso.Rows.Count == 0) throw new Exception("loi:Lỗi không lấy được tham số:loi");
            DataRow _row = b_dt_tso.Rows[0];
            DataTable b_dt = c_nhapdiem.Fdt_SINHVIEN_DK_TIM(LibConvert.toString(_row["lop"]), LibConvert.toString(_row["nam_hoc"]), LibConvert.toString(_row["mon_hoc"]),
                LibConvert.toString(_row["hoc_ky"]), LibConvert.toString(_row["loai_diem"]));
            return LibTable.FormTableToJson(b_dt);
        }
        catch (Exception ex)
        {
            return "loi:" + ex.Message + ":loi";
        }
    }
    [OperationContract]
    public string Fs_DIEM_MONLKE(string id_tkb, string b_ma, string b_mon, string hk)
    {
        try
        {
            DataTable b_dt = c_nhapdiem.Fdt_LKE_DSDIEM(id_tkb, b_ma, b_mon, hk);
            return LibTable.FormTableToJson(b_dt);
        }
        catch (Exception ex)
        {
            return "loi:" + ex.Message + ":loi";
        }
    }
    [OperationContract]
    public string Fs_DIEM_NH(object[] a_gtri, string[] a_ten, object[] a_gtriGrid)
    {
        try
        {
            DataTable b_dt = LibBang.Fdt_TAO_BANG(LibConvert.Fa_OBJ_STR(a_ten), LibConvert.Fa_OBJ_STR(a_gtri));
            if (b_dt.Rows.Count == 0) throw new Exception("loi:Không lấy được số liệu:loi");
            object b_return = "";
            DataRow b_dr = b_dt.Rows[0];

            //if (string.IsNullOrEmpty(id_dk))
            //{
            //    b_return = SqlData.ExeCuteProcedure(new object[] { id_dk, id_tkb, ma_sv, LibConvert.OBJ_STR(b_dr["mon_hoc"]) }, "@id_dk", "PNHAP_DKY")[0];
            //}
            luu.ht_nsd _nsd = new luu.ht_nsd();
            DataTable b_dt_tinh = new DataTable();
            LibBang.P_THEM_COT(ref b_dt_tinh, new string[] { "id", "id_dk", "id_loai", "diem", "nsd" });
            for (int i = 0; i < a_gtriGrid.Length; i++)
            {
                string[] a_loai = new string[b_dt_tinh.Columns.Count];
                a_loai[0] = ((object[])a_gtriGrid[i])[0].ToString();
                a_loai[1] = ((object[])a_gtriGrid[i])[1].ToString();
                a_loai[2] = LibConvert.OBJ_STR(b_dr["loai_diem"]);
                a_loai[3] = (((object[])(a_gtriGrid[i]))[2]==null? null: ((object[])(a_gtriGrid[i]))[2].ToString());
                a_loai[4] = _nsd.user;
                LibBang.P_THEM_HANG(ref b_dt_tinh, a_loai);
            }
            SqlData.ExeCuteProcedure(b_dt_tinh, "PNHAP_DIEM");
            return Fs_SINHVIEN_DK_TIM(a_ten, a_gtri);
        }
        catch (Exception ex)
        {
            return "loi:" + ex.Message + ":loi";
        }
    }
    [OperationContract]
    public string Fs_DN_DIEM_LKE(string id_nam)
    {
        try
        {
            return c_nhapdiem.Fs_DN_DIEM_LKE(id_nam);
        }
        catch (Exception ex)
        {
            return "loi:" + ex.Message + ":loi";
        }
    }
    [OperationContract]
    public string Fs_DN_DIEM_NH(string id, string id_nam, string ct)
    {
        try
        {
            return c_nhapdiem.Fs_DN_DIEM_NH(id, id_nam, ct)[0].ToString();
        }
        catch (Exception ex)
        {
            return "loi:" + ex.Message + ":loi";
        }
    }

    #region Khối thi
    [OperationContract]
    public string Fs_KHOI_THI_LKE()
    {
        try
        {
            DataTable b_dt = c_nhapdiem.Fdt_KHOI_THI_LKE();
            return LibTable.FormTableToJson(b_dt);
        }
        catch (Exception ex)
        {
            return "loi:" + ex.Message + ":loi";
        }
    }
    [OperationContract]
    public string Fs_KHOI_THI_NH(string id, string khoi, string mon, string hso)
    {
        try
        {
            string b_id = c_nhapdiem.Fdt_KHOI_THI_NH(id, khoi, mon, LibConvert.ToFloat(hso));
            return Fs_KHOI_THI_LKE() + "@" + b_id;
        }
        catch (Exception ex)
        {
            return "loi:" + ex.Message + ":loi";
        }
    }
    [OperationContract]
    public string Fs_KHOI_THI_XOA(string id)
    {
        try
        {
            c_nhapdiem.Fdt_KHOI_THI_LKE();
            return Fs_KHOI_THI_LKE() + "@" + "";
        }
        catch (Exception ex)
        {
            return "loi:" + ex.Message + ":loi";
        }
    }

    #endregion

   
    [OperationContract]
    public string Fs_NganhDT(string id_nganhhoc)
    {
        try
        {
            DataTable b_dt = c_ht.Fdt_NGANHDT_NGANHHOC_LKE(id_nganhhoc);
            return LibTable.FormTableToJson(b_dt);
        }
        catch (Exception ex)
        {
            return "loi:" + ex.Message + ":loi";
        }
    }

    #region Khóa sổ
    [OperationContract]
    public string Fs_NAM_HOC()
    {
        try
        {
            DataTable b_dt_namhoc = c_ht.Fdt_NAM_HOC();
            DataTable b_dt_bm = c_ht.Fdt_BOMON_LKE();
            return LibTable.FormTableToJson(b_dt_namhoc) + "@" + LibTable.FormTableToJson(b_dt_bm);
        }
        catch (Exception ex)
        {
            return "loi:" + ex.Message + ":loi";
        }
    }
    [OperationContract]
    public string Fs_KHOA_SO(string nam, string hk)
    {
        try
        {
            LIB.Data.SqlData.ExeCuteProcedure(new object[] { nam, hk }, "PKHOA_SO");
            return "loi:Khóa sổ thành công:loi";
        }
        catch (Exception ex)
        {
            return "loi:" + ex.Message + ":loi";
        }
    }
    #endregion

    #region Xet học bổng
    [OperationContract]
    public string Fs_TT_CHUNG()
    {
        try
        {
            DataTable b_dt_namhoc = c_ht.Fdt_NAM_HOC();
            DataTable b_bac_dt = LIB.Data.SqlData.GetData("PHinhThuc_DT_LKE");
            return LibTable.FormTableToJson(b_dt_namhoc) + "@" + LibTable.FormTableToJson(b_bac_dt);
        }
        catch (Exception ex)
        {
            return "loi:" + ex.Message + ":loi";
        }
    }
    [OperationContract]
    public string Fs_XET_HB_LKE(string nam)
    {
        try
        {
            DataTable b_dt = LIB.Data.SqlData.GetData(nam, "PXET_HOC_BONG_LKE");
            return LibTable.FormTableToJson(b_dt);
        }
        catch (Exception ex)
        {
            return "loi:" + ex.Message + ":loi";
        }
    }
    [OperationContract]
    public string Fs_XET_HB_NH(string nam, string dk, string gchu)
    {
        try
        {
            LIB.Data.SqlData.ExeCuteProcedure(new object[] { nam, (dk == "" ? "0" : dk), gchu }, "PXET_HOC_BONG_NH");
            return "loi:Thêm thành công:loi";
        }
        catch (Exception ex)
        {
            return "loi:" + ex.Message + ":loi";
        }
    }
    [OperationContract]
    public string Fs_XET_HB_XOA(string nam)
    {
        try
        {
            LIB.Data.SqlData.ExeCuteProcedure(nam, "PXET_HOC_BONG_XOA");
            return "loi:Xóa thành công:loi";
        }
        catch (Exception ex)
        {
            return "loi:" + ex.Message + ":loi";
        }
    }

    #endregion

    #region PHân quyền
    [OperationContract]
    public string Fs_PHAN_QUYEN_TTCH()
    {
        try
        {
            luu.ht_nsd _nsd = new luu.ht_nsd();
            if (_nsd == null)
                throw new Exception("loi:Mat ket noi:loi");
            string nsd = _nsd.user;
            DataSet b_ds = new DataSet();
            b_ds = LIB.Data.SqlData.GetMutilData(new object[] { nsd }, "PPHANQUYEN_TTCH");

            if (b_ds.Tables.Count < 2)
                throw new Exception("loi:Lỗi lấy dữ liệu:loi");


            return LibTable.FormTableToJson(b_ds.Tables[0]) + "@" + LibTable.FormTableToJson(b_ds.Tables[1]);
        }
        catch (Exception ex)
        {
            return "loi:" + ex.Message + ":loi";
        }
    }
    [OperationContract]
    public string Fs_PHAN_QUYEN_CT(string id)
    {
        try
        {
            DataTable b_dt = new DataTable();
            b_dt = LIB.Data.SqlData.GetData(id, "PPHANQUYEN_CT");
            if (b_dt.Rows.Count < 1)
                throw new Exception("loi:Lỗi lấy dữ liệu:loi");
            return LibTable.FormTableToJson(b_dt);
        }
        catch (Exception ex)
        {
            return "loi:" + ex.Message + ":loi";
        }
    }
    //Xóa user
    [OperationContract]
    public string Fs_PHAN_QUYEN_XOA(string id_user)
    {
        try
        {
            SqlData.ExeCuteProcedure(id_user, "PUSER_XOA");
            return Fs_PHAN_QUYEN_TTCH();
        }
        catch (Exception e)
        {
            return e.Message;
        }
    }
    [OperationContract]
    public string Fs_PHAN_QUYEN_THEM(string b_id_user, string b_userName, string b_pass, string id_ns,
            string b_ten, string b_dc, string b_email, string b_phong, string b_ten_ph, object[] a_gtriGrid)
    {
        try
        {
            object[] id = SqlData.ExeCuteProcedure(new object[] { b_id_user, b_userName, b_pass, id_ns, b_ten, b_dc, b_email, b_phong, b_ten_ph }, "@id", "PPHANQUYEN_USER_NH");
            b_id_user = id[0].ToString();
            DataTable b_dt = new DataTable();
            LibTable.AddNewCol(ref b_dt, new string[] { "id", "modun", "user", "xem", "them", "xoa" });
            for (int i = 0; i < a_gtriGrid.Length; i++)
            {
                string[] a_gtri = new string[b_dt.Columns.Count];
                a_gtri[0] = LibConvert.toString(((object[])a_gtriGrid[i])[0]);
                a_gtri[1] = LibConvert.toString(((object[])a_gtriGrid[i])[1]);
                a_gtri[2] = id[0].ToString();
                a_gtri[3] = LibConvert.toString(((object[])a_gtriGrid[i])[2]);
                a_gtri[4] = LibConvert.toString(((object[])a_gtriGrid[i])[3]);
                a_gtri[5] = LibConvert.toString(((object[])a_gtriGrid[i])[4]);
                LibTable.AddNewRow(ref b_dt, a_gtri);
            }
            SqlData.ExeCuteProcedure(b_dt, "PPHANQUYEN_QUYEN_NH");
            return Fs_PHAN_QUYEN_TTCH();
        }
        catch (Exception ex)
        {
            Fs_PHAN_QUYEN_XOA(b_id_user);
            return "loi:" + ex.Message + ":loi";
        }
    }
    #endregion

    #region Lớp hoc
    [OperationContract]
    public string Fs_LOP_HOC()
    {
        try
        {
            DataTable b_bm = c_ht.Fdt_BOMON_LKE();

            DataTable b_bac_dt = c_ht.Fdt_DT_LKE();

            DataTable b_dt_dt = LIB.Data.SqlData.GetData("PHinhThuc_DT_LKE");

            return LibTable.FormTableToJson(b_dt_dt) + "@" + LibTable.FormTableToJson(b_bac_dt) + "@" + LibTable.FormTableToJson(b_bm);
        }
        catch (Exception ex)
        {
            return "loi:" + ex.Message + ":loi";
        }
    }

    [OperationContract]
    public string Fs_LOPNHANG(string id_nganh)
    {
        try
        {
            DataTable b_dt_lop = c_ht.Fdt_LOP_HOC_LKE(id_nganh);
            return LibBang.Fs_BANG_JSON(b_dt_lop);
        }
        catch (Exception ex)
        {
            return ex.Message;
        }
    }
    //Lớp học - khóa
    [OperationContract]
    public string Fs_LOP_KHOA(string id_khoa)
    {
        try
        {
            DataTable b_dt_lop = c_ht.Fdt_LOP_HOC_KHOA_LKE(id_khoa);
            return LibBang.Fs_BANG_JSON(b_dt_lop);
        }
        catch (Exception ex)
        {
            return ex.Message;
        }
    }

    [OperationContract]
    public string Fs_LOPHOC_NH(string id, string ten, string id_khoa, string id_he_dt)
    {
        try
        {
            SqlData.ExeCuteProcedure(new object[] { id, ten, id_khoa, id_he_dt }, "PLOP_HOC_NH");
            return Fs_LOP_KHOA(id_khoa);
        }
        catch (Exception ex)
        {
            return "loi:" + ex.Message + "loi:";
        }
    }
    [OperationContract]
    public string Fs_LOPHOC_XOA(string idLop, string id_khoa)
    {
        try
        {
            SqlData.ExeCuteProcedure(new object[] { idLop }, "PLOP_HOC_XOA");
            return Fs_LOP_KHOA(id_khoa);
        }
        catch (Exception ex)
        {
            return ex.Message;
        }
    }
    #endregion

    #region Rèn Luyên

    [OperationContract]
    public string Fs_SINHVIEN_RL_TIM(string nam, string hk, string lop)
    {
        try
        {
            luu.ht_nsd nsd = new luu.ht_nsd();
            DataTable b_dt = LIB.Data.SqlData.GetData(new object[] { nam, hk, lop, nsd.user }, "PDIEM_RL_LKE");
            return LibTable.FormTableToJson(b_dt);
        }
        catch (Exception ex)
        {
            return "loi:" + ex.Message + ":loi";
        }
    }
    [OperationContract]
    public string Fs_SINHVIEN_RL_NH(string nam, string hk, string lop, object[] a_gtriGrid)
    {
        try
        {
            DataTable b_dt_tso = new DataTable();
            LibTable.AddNewCol(ref b_dt_tso, new string[] { "id_dk", "nam_hoc", "hk", "ma_sv", "diem" });
            for (int i = 0; i < a_gtriGrid.Length; i++)
            {
                string[] a_gtri = new string[5];
                a_gtri[0] = LibConvert.toString(((object[])a_gtriGrid[i])[0]);
                a_gtri[1] = LibConvert.toString(nam);
                a_gtri[2] = LibConvert.toString(hk);
                a_gtri[3] = LibConvert.toString(((object[])a_gtriGrid[i])[1]);
                a_gtri[4] = LibConvert.toString(((object[])a_gtriGrid[i])[2]);

                LibTable.AddNewRow(ref b_dt_tso, a_gtri);
            }

            LIB.Data.SqlData.ExeCuteProcedure(b_dt_tso, "PDIEM_REN_LUYEN_NH");
            return Fs_SINHVIEN_RL_TIM(nam, hk, lop);
        }
        catch (Exception ex)
        {
            return "loi:" + ex.Message + ":loi";
        }
    }

    #endregion

    #region Môn tốt nghiệp
    [OperationContract]
    public string Fs_MON_TN_LKE(string hedt, string loai)
    {
        try
        {
            return LibTable.FormTableToJson(SqlData.GetData(new object[] { hedt, loai }, "PMON_TN_LKE"));
            //
        }
        catch (Exception ex)
        {
            return "loi" + ex.Message + ":loi";
        }
    }
    [OperationContract]
    public string Fs_MON_TN_NH(string id, string hedt, string loai, string monid)
    {
        try
        {
            SqlData.ExeCuteProcedure(new object[] { id, 2005, hedt, loai, monid }, "PMON_TN_NH");
            return LibTable.FormTableToJson(SqlData.GetData(new object[] { hedt, loai }, "PMON_TN_LKE"));
            //
        }
        catch (Exception ex)
        {
            return "loi" + ex.Message + ":loi";
        }
    }
    [OperationContract]
    public string Fs_MON_TN_XOA(string id, string hedt, string loai)
    {
        try
        {
            SqlData.ExeCuteProcedure(id, "PMON_TN_XOA");
            return LibTable.FormTableToJson(SqlData.GetData(new object[] { hedt, loai }, "PMON_TN_LKE"));
            //
        }
        catch (Exception ex)
        {
            return "loi" + ex.Message + ":loi";
        }
    }
    #endregion
    [OperationContract]
    public string Fs_DIEM_HK()
    {
        try
        {
            DataTable b_dt_namhoc = c_ht.Fdt_NAM_HOC();

            //Thêm hệ dt
            DataTable b_dt_he = LIB.Data.SqlData.GetData("PHinhThuc_DT_LKE");

            return LibTable.FormTableToJson(b_dt_namhoc) + "@" + LibTable.FormTableToJson(b_dt_he);
        }
        catch (Exception ex)
        {
            return "loi:" + ex.Message + ":loi";
        }
    }
    [OperationContract]
    public string Fs_DIEM_HK_BC(string b_loai, string nam, string ten, string lop, string hk, string b_ten_lop)
    {
        try
        {
            DataTable b_dt_dk = new DataTable();
            DataTable b_dt_tso = new DataTable();
            string xml="";
            if (b_loai == "HK")
            {
                DataSet ds= SqlData.GetMutilData(new object[] { nam, hk, lop }, "PTKE_DIEM_HK");
                System.Web.HttpContext.Current.Session["kq_ds"] = "";
                System.Web.HttpContext.Current.Session["kq_ds"] = ds;
                System.Web.HttpContext.Current.Session["loai"] = "";
                System.Web.HttpContext.Current.Session["loai"] = "1";
                return "1@"+ hk;
            }
            else if (b_loai == "TK")
            {
                b_dt_dk = SqlData.GetData(new object[] { nam, hk, lop }, "PDIEM_HOCKY_CHUNG");
                LibTable.AddNewCol(ref b_dt_tso, new string[] { "NAM", "LOP" });
                LibTable.AddNewRow(ref b_dt_tso, new string[] { "CUỐI KỲ " + hk.ToString()+ " NĂM "+ten,"Lớp: "+ b_ten_lop });
                xml = "diem_tb_nam";
            }
            else if (b_loai == "3")
            {
                b_dt_dk = SqlData.GetData(new object[] { nam, lop }, "PSINHVIEN_TN_BC");
                LibTable.AddNewCol(ref b_dt_tso, "LOP");
                LibTable.AddNewRow(ref b_dt_tso, "LỚP " + ten);
                xml = "ds_diem_tn";
            }
            else if (b_loai == "CN")
            {
                DataSet ds = SqlData.GetMutilData(new object[] { lop, hk }, "PDIEM_CUOI_NAM");
                System.Web.HttpContext.Current.Session["kq_ds"] = "";
                System.Web.HttpContext.Current.Session["kq_ds"] = ds;
                System.Web.HttpContext.Current.Session["loai"] = "";
                System.Web.HttpContext.Current.Session["loai"] = "2";
                return "2@" + ten;
            }
            else
            {
                b_dt_dk = SqlData.GetData(new object[] { nam, hk, lop }, "PTKE_DIEM_HK_CT");
                LibTable.AddNewCol(ref b_dt_tso, "TEN");
                LibTable.AddNewRow(ref b_dt_tso, ten);
                xml = "bc_diem_ct";
            }
            DataSet b_ds = new DataSet();

            b_dt_tso.TableName = "B1";
            b_dt_dk.TableName = "B";
            b_ds.Tables.Add(b_dt_tso.Copy());
            b_ds.Tables.Add(b_dt_dk.Copy()); b_ds.AcceptChanges();
            string b_out = "~/Outputs/";
            ht_bc.P_XUAT_EXCEL("~/Templates/", ref b_out, xml, b_ds);
            string[] url = b_out.Split('/');
            return url[url.Length - 1];
        }
        catch (Exception ex)
        {
            return "loi:" + ex.Message + ":loi";
        }
    }

    #region TỐt nghiệp

    [OperationContract]
    public string Fs_SINHVIEN_TN_TIM(string lop)
    {
        try
        {
            DataTable b_dt = SqlData.GetData(new object[] { lop }, "PSINH_TN_TIM");
            return LibTable.FormTableToJson(b_dt);
        }
        catch (Exception ex)
        {
            return "loi:" + ex.Message + ":loi";
        }
    }
    //Liệt kê\

    [OperationContract]
    public string Fs_SINHVIEN_TN_CT(string ma_sv, string lopID, string nam)
    {
        try
        {
            DataTable b_dt = SqlData.GetData(new object[] { ma_sv, lopID, nam }, "PSINHVIEN_TN_CT");
            return LibTable.FormTableToJson(b_dt);
        }
        catch (Exception ex)
        {
            return "loi:" + ex.Message + ":loi";
        }
    }

    [OperationContract]
    public string Fs_SINHVIEN_TN_NH(string nam, string lopID, string b_hedt, object[] a_dt)
    {
        try
        {
            DataTable b_dt = new DataTable();
            LibTable.AddNewCol(ref b_dt, new string[] { "id", "ma_sv", "namhoc", "he_dt", "idmon", "diem", "hso", "ndung" });
            for (int i = 0; i < a_dt.Length; i++)
            {
                string[] a = new string[8];
                a[0] = LibConvert.toString(((object[])a_dt[i])[0]);
                a[1] = LibConvert.toString(((object[])a_dt[i])[1]);
                a[2] = nam;
                a[3] = b_hedt;
                a[4] = LibConvert.toString(((object[])a_dt[i])[2]);
                a[5] = LibConvert.toString(((object[])a_dt[i])[3]);
                a[6] = LibConvert.toString(((object[])a_dt[i])[4]);
                a[7] = "";
                LibTable.AddNewRow(ref b_dt, a);
            }
            SqlData.ExeCuteProcedure(b_dt, "PSINHVIEN_TN_NH");
            return Fs_SINHVIEN_TN_CT(((object[])a_dt[0])[1].ToString(), lopID, nam);           
        }
        catch (Exception ex)
        {
            return "loi:" + ex.Message + ":loi";
        }
    }
    #endregion

    #region Nhập thông tin sinh viên
    [OperationContract]
    public string Fs_SINHVIEN_NH(string loai, string b_url, string[] a_ten, string[] a_gtri, object[] a_qhgd)
    {
        try
        {
            DataTable b_dt = LibBang.Fdt_TAO_BANG(a_ten, a_gtri);
            if (b_dt.Rows.Count == 0) throw new Exception("Không lấy được dữ liệu");
            DataRow b_dr = b_dt.Rows[0];
            object[] b_gtri = new object[] { loai, b_url,
            LibConvert.toString(b_dr["ma_sv"]),  LibConvert.toString(b_dr["lop"]), LibConvert.toString(b_dr["khoa_dt"])
            , LibConvert.toString(b_dr["Hodem"]), LibConvert.toString(b_dr["Ten"]), LibConvert.DateToNumber(b_dr["ngaysinh"]), LibConvert.toString(b_dr["Noi_sinh"]), 
            LibConvert.toString(b_dr["thuongtru"])
            , LibConvert.toString(b_dr["doituong"]), LibConvert.toString(b_dr["gioitinh"]), LibConvert.toString(b_dr["dantoc"]), LibConvert.toString(b_dr["khuvuc"])
            , LibConvert.toString(b_dr["cmt"]), LibConvert.DateToNumber(b_dr["ngaycap"]), LibConvert.toString(b_dr["noi_cap"]), LibConvert.toString(b_dr["sdt"])            
            , LibConvert.DateToNumber(b_dr["ngay_nn"]), LibConvert.DateToNumber(b_dr["ngay_xn"])
            };
            SqlData.ExeCuteProcedure(b_gtri, "PSINHVIEN_NH");
            // Nhap thong tin gia dinh
            DataTable b_dt_qh = new DataTable();
            LibTable.AddNewCol(ref  b_dt_qh, new string[] { "QH_ID", "MA_SV", "loai", "hoten", "nam_sinh", "nghe_nghiep", "dan_toc", "ton_giao", "hktt" });
            for (int i = 0; i < a_qhgd.Length; i++)
            {
                string[] a_gtriQH = new string[9];
                a_gtriQH[0] = LibConvert.toString(((object[])a_qhgd[i])[0]);
                a_gtriQH[1] = LibConvert.toString(b_dr["ma_sv"]);
                a_gtriQH[2] = LibConvert.toString(((object[])a_qhgd[i])[1]);
                a_gtriQH[3] = LibConvert.toString(((object[])a_qhgd[i])[2]);
                a_gtriQH[4] = LibConvert.toString(((object[])a_qhgd[i])[3]);
                a_gtriQH[5] = LibConvert.toString(((object[])a_qhgd[i])[4]);
                a_gtriQH[6] = LibConvert.toString(((object[])a_qhgd[i])[5]);
                a_gtriQH[7] = LibConvert.toString(((object[])a_qhgd[i])[6]);
                a_gtriQH[8] = LibConvert.toString(((object[])a_qhgd[i])[7]);

                LibTable.AddNewRow(ref b_dt_qh, a_gtriQH);
            }
            SqlData.ExeCuteProcedure(b_dt_qh, "PSINHVIEN_QHGD_NH");
            return "";
        }
        catch (Exception ex)
        {
            return "loi:" + ex.Message + ":loi";
        }
    }

    [OperationContract]
    public string Fs_SINHVIEN_NH_EXCEL(DataTable a)
    {
        SqlData.ExeCuteProcedure(a, "PSINHVIEN_NH_EXCEL");
        return "";
    }
    [OperationContract]
    public string Fs_SINHVIEN_TIM_MA(string ma_sv)
    {
        try
        {
            DataSet b_ds = SqlData.GetMutilData(new object[] { ma_sv }, "PSINHVIEN_TIM_MA");
            DataTable b_dt_sv = new DataTable(); b_dt_sv = b_ds.Tables[0];
            if (b_dt_sv.Rows.Count == 0) throw new Exception("Không có số liệu");
            string b_hedt= LibConvert.toString(b_dt_sv.Rows[0]["he_dt"]);
            DataTable b_khoadt = c_ht.Fdt_KHOA_HEDAOTAO(b_hedt);

            string b_nganhID = LibConvert.toString(b_dt_sv.Rows[0]["khoa_dt"]);
            DataTable b_dt_lop = c_ht.Fdt_LOP_HOC_LKE(b_nganhID);
            DataTable b_dt_qhgd = new DataTable();
            LibBang.P_THEM_COT(ref b_dt_qhgd, new string[] { "QH_ID", "ma_sv", "loai", "hoten", "nam_sinh", "nghe_nghiep", "dan_toc", "hktt", "ton_giao" });

            foreach (DataRow br in b_ds.Tables[1].Rows)
                b_dt_qhgd.ImportRow(br);
            for (int i = b_dt_qhgd.Rows.Count + 1; i < 8; i++)
                LibTable.AddNewRow(ref b_dt_qhgd, "");
            return LibTable.FormTableToJson(b_ds.Tables[0]) + "@" + LibTable.FormTableToJson(b_dt_qhgd) +
                "@ " + LibTable.FormTableToJson(b_khoadt) + "@" + LibTable.FormTableToJson(b_dt_lop);
        }
        catch (Exception ex)
        {
            return "loi:" + ex.Message + ":loi";
        }
    }
    [OperationContract]
    public string Fs_SINHVIEN_XOA(string ma_sv, string b_loai_dt, string b_lop)
    {
        try
        {
            SqlData.ExeCuteProcedure(ma_sv, "PSINHVIEN_XOA");
            return LibTable.FormTableToJson(c_ht.Fdt_TIM_SINHVIEN("", "", b_lop));
        }
        catch (Exception ex)
        {
            return "loi:" + ex.Message + ":loi";
        }
    }
    #endregion

    [OperationContract]
    public string Fs_DS_THILAI(string b_loai, string b_namhoc, string b_hk, string b_lop, string b_mon, string b_ten_lop, string b_ten_mon)
    {
        try
        {
            DataTable b_dt_dk = new DataTable();
            DataTable b_dt_tso = new DataTable();

            b_dt_dk = SqlData.GetData(new object[] { b_namhoc, b_lop, b_hk, b_mon }, "PSINHVIEN_THILAI");
            if (b_dt_dk.Rows.Count == 0) throw new Exception("Không có số liệu");
            LibTable.AddNewCol(ref b_dt_tso, new string[] { "LOP", "MON" });
            LibTable.AddNewRow(ref b_dt_tso, new string[] { "Lớp: " + b_ten_lop, "Môn: " + b_ten_mon });
            DataSet b_ds = new DataSet();
            if (b_loai == "E")
            {
                b_dt_tso.TableName = "B1";
                b_dt_dk.TableName = "B";
                b_ds.Tables.Add(b_dt_tso.Copy());
                b_ds.Tables.Add(b_dt_dk.Copy()); b_ds.AcceptChanges();
                string b_out = "~/Outputs/";
                ht_bc.P_XUAT_EXCEL("~/Templates/", ref b_out, "eXML_ds_hs_tl", b_ds);
                string[] url = b_out.Split('/');
                return url[url.Length - 1];
            }
            else
            {
                return LibTable.FormTableToJson(b_dt_dk);
            }
        }
        catch (Exception ex)
        {
            return "loi:" + ex.Message + ":loi";
        }
    }
    [OperationContract]
    public string Fs_DS_THILAI_NAM(string b_namhoc, string b_ten)
    {
        try
        {
            DataTable b_dt_dk = new DataTable();
            DataTable b_dt_tso = new DataTable();

            b_dt_dk = SqlData.GetData(b_namhoc, "PSINH_VIEN_TL_NAM");
            if (b_dt_dk.Rows.Count == 0) throw new Exception("Không có số liệu");
            LibTable.AddNewCol(ref b_dt_tso, new string[] { "LOP" });
            LibTable.AddNewRow(ref b_dt_tso, new string[] { "Năm học: " + b_ten });
            DataSet b_ds = new DataSet();

            b_dt_tso.TableName = "B1";
            b_dt_dk.TableName = "B";
            b_ds.Tables.Add(b_dt_tso.Copy());
            b_ds.Tables.Add(b_dt_dk.Copy()); b_ds.AcceptChanges();
            string b_out = "~/Outputs/";
            ht_bc.P_XUAT_EXCEL("~/Templates/", ref b_out, "eXML_ds_hs_tl_nam", b_ds);
            string[] url = b_out.Split('/');
            return url[url.Length - 1];
        }
        catch (Exception ex)
        {
            return "loi:" + ex.Message + ":loi";
        }
    }
    //Nhập điêm thi lại
    [OperationContract]
    public string Fs_NHAP_DIEM_THILAI(string b_namhoc, string b_mon, string b_hk, object[] a_gtr)
    {
        try
        {
            DataTable b_dt = new DataTable();
            LibTable.AddNewCol(ref b_dt, new string[] { "id_dk", "diem", "nam", "mon", "ma_sv" });
            string[] a_gtri = new string[b_dt.Columns.Count];
            for (int i = 0; i < a_gtr.Length; i++)
            {
                a_gtri[0] = LibConvert.toString(((object[])a_gtr[i])[1]);
                a_gtri[1] = LibConvert.toString(((object[])a_gtr[i])[2]);
                a_gtri[2] = b_namhoc;
                a_gtri[3] = b_mon;
                a_gtri[4] = LibConvert.toString(((object[])a_gtr[i])[0]);

                LibTable.AddNewRow(ref b_dt, a_gtri);
            }
            SqlData.ExeCuteProcedure(b_dt, "PSINH_VIEN_TL_NHDIEM");
            return "";
        }
        catch (Exception ex)
        {
            return "loi:" + ex.Message + ":loi";
        }
    }



    //tungns
    //Đăng ký học
    [OperationContract]
    public string Fs_Dang_Ky_Hoc()
    {
        try
        {
            DataTable b_dt_namhoc = c_ht.Fdt_NAM_HOC();

            return LibTable.FormTableToJson(b_dt_namhoc);
        }
        catch (Exception ex)
        {
            return "loi:" + ex.Message + ":loi";
        }
    }
    [OperationContract]
    public string Fs_DS_DANG_KY_HOC(string b_ma_sv, string b_namhoc, string b_hk)
    {
        try
        {
            DataTable b_dt = c_nhapdiem.Fdt_LKE_DSDANGKY_HOC(b_ma_sv, b_namhoc, b_hk);
            return LibTable.FormTableToJson(b_dt);
        }
        catch (Exception ex)
        {
            return "loi:" + ex.Message + ":loi";
        }
    }

    #region Mở khóa
    [OperationContract]
    public string Fs_MO_KHOA_SO(string namhoc, string b_hk, object[] a_grid)
    {
        try
        {
            DataTable b_dt = new DataTable();
            LibTable.AddNewCol(ref b_dt, new string[] { "nam_hoc", "hoc_ky", "id_lop"});
            for (int i = 0; i < a_grid.Length; i++)
            { 
              object[] a_gtri=new object[3];
              a_gtri[0] = namhoc;
              a_gtri[1] = b_hk;
              a_gtri[2] = a_grid[i];
              LibTable.AddNewRow(ref b_dt, a_gtri);
            }
            SqlData.ExeCuteProcedure(b_dt, "PKS_MO_KHOA");
            return "";
        }
        catch (Exception ex)
        {
            return "loi:" + ex.Message + ":loi";
        }
    }
    #endregion

    #region Báo cáo

    [OperationContract]
    public string Fs_DIEM_MON_HK(string nam, string mon, string hk)
    {
        try
        {
            DataSet set = new DataSet();
            DataTable b_dt = SqlData.GetData(new object[] { nam, mon, hk }, "PTHONG_KE_DIEM_MON");
            DataTable b_dt_tso = new DataTable();
            set.Tables.Add(b_dt.Copy());
            b_dt_tso.TableName = "TENBC";
            DataTable b_tso = new DataTable("TSO");
            set.Tables.Add(b_dt_tso.Copy());
            set.Tables.Add(b_tso.Copy());
            string str = "~/outputs/";
            htin.P_XUAT_EXCEL("~/App_rpt/sl/", ref str, "eXML_b0205_sldt_2013", set);
            return str;
        }
        catch (Exception ex)
        {
            return "loi:" + ex.Message + ":loi";
        }
    }
    #endregion
}
